package com.powerunion.datacollection.persistence.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.powerunion.util.DBUtils;

public class DBHelper {

	/**
	 * 获取数据库系统的用户
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static String[] getDBUsers(Connection conn) throws SQLException {
		return getDBUsers(conn.getMetaData());
	}
	
	/**
	 * 获取数据库系统的用户
	 * @param metaData
	 * @return
	 * @throws SQLException
	 */
	public static String[] getDBUsers(DatabaseMetaData metaData)
		throws SQLException {
		ResultSet rs = metaData.getSchemas();
		List users = new ArrayList();
		while(rs.next()) {
			users.add(rs.getString("TABLE_SCHEM"));
		}
		rs.close();
		String result[] = new String[users.size()];
		users.toArray(result);
		return result;
	}
	
	public static String[] getDatabases(Connection conn) throws
		SQLException {
		return getDatabases(conn.getMetaData());
	}
	/**
	 * 获取数据库系统的名称
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static String getDBMSName(Connection conn) throws SQLException {
		DatabaseMetaData dmd = conn.getMetaData();
		return dmd.getDatabaseProductName();
	}
	
	/**
	 * 获取数据库系统的所有数据库名称
	 * @param metaData
	 * @return
	 * @throws SQLException
	 */
	public static String[] getDatabases(DatabaseMetaData metaData)
		throws SQLException {
		ResultSet rs = metaData.getCatalogs();
		List databases = new ArrayList();
		while(rs.next()) {
			databases.add(rs.getString("TABLE_CAT"));
		}
		rs.close();
		String result[] = new String[databases.size()];
		databases.toArray(result);
		return result;
	}
	
	/**
	 * 获取数据库的所有表名称
	 * @param conn
	 * @param user
	 * @param dataBase
	 * @return
	 * @throws SQLException
	 */
	public static String[] getDBTables(Connection conn,String user,
				String dataBase) throws SQLException {
		return getDBTables(conn.getMetaData(),user,dataBase);
	}
	
	/**
	 * 获取数据库的所有表名称
	 * @param metaData
	 * @param dataBase
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public static String[] getDBTables(DatabaseMetaData metaData,String dataBase,
			String user) throws SQLException {
		ResultSet rs = metaData.getTables(dataBase, null, user, new String[]{"TABLE"});
		List tables = new ArrayList();
		while(rs.next()) {
			tables.add(rs.getString("TABLE_NAME"));
		}
		rs.close();
		String result[] = new String[tables.size()];
		tables.toArray(result);
		return result;
	}
	
	public static Set getTableFields(String tableName,
			Connection connection) throws SQLException {
		Set columns = new HashSet();
		ResultSet result = null;
		try{
			result = connection.getMetaData().getColumns(null, null, tableName, null);
			while(result.next()) {
				if(result.getString(4) != null) {
					columns.add(result.getString(4));
				}
			}
		}finally{
			DBUtils.close(result,null, connection);
		}
		return columns;
	}
	
	public static String getTablePrimaryKeyFieldName(String tableName,Connection connection)
		throws SQLException {
		String k = null;
		ResultSet result = null;
		try{
			result = connection.getMetaData().getPrimaryKeys(null, null, tableName);
			if(result.next()) {
				k = result.getString(4);
			}
		}finally{
			DBUtils.close(result,null, connection);
		}
		return k;
	}
	
}
